﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Windows.Forms;

using Model_Layer;

namespace Data_Access_Layer
{
    public class DBAirport : IDBAirport
    {
        private SqlConnection con;

        public DBAirport()
        {
            con = Connection.GetInstance().GetConnection();
        }

        #region Methods for getting the adjacentcy list
        
        #region GetAdjacentcyListIds

        public List<int> GetIdAdjacentcyList(int idairport)
        {   
            SqlDataReader reader = null;

            string idquery = "SELECT DISTINCT Flight.iddestinationairport " +
            "FROM Flight INNER JOIN Airport ON Flight.idstartairport=Airport.idairport " +
            "WHERE Airport.idairport = " + idairport;

            List<int> idDestinationAirportList = new List<int>();

            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(idquery, con);
                reader = cmd.ExecuteReader();

                while(reader.Read())
                {

                    int idDestinationAirport; 
                    idDestinationAirport = int.Parse(reader["iddestinationairport"].ToString());
                    idDestinationAirportList.Add(idDestinationAirport);
                    //MessageBox.Show(idDestinationAirport.ToString());
                }
                con.Close();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return idDestinationAirportList;
        }
        #endregion

        #region GetAdjacentcyListFromIds
        public delegate List<Airport> GetAdjacentcyListDelegate(List<int> method);

        public List<Airport> GetAdjacentcyList(List<int> GetIdAdjacentcyList)
        {
            SqlDataReader reader = null;

            List<int> idDestinationAirportList2 = new List<int>();
            idDestinationAirportList2 = GetIdAdjacentcyList;

            List<Airport> adjAirportList = new List<Airport>();
            foreach(int iddestinationairport in idDestinationAirportList2)
            {
                string idquery = "SELECT * FROM Airport" +
                    "WHERE Airport.idairport = " + "'" + iddestinationairport + "'";

                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand(idquery, con);
                    reader = cmd.ExecuteReader();

                    while(reader.Read())
                    {
                        Airport adjAirportObj = new Airport();
                        adjAirportObj.IdAirport = int.Parse(reader["Flight.iddestinationairport"].ToString());
                        adjAirportObj.Name = reader["name"].ToString();
                        adjAirportObj.Acronym = reader["acronym"].ToString();
                        adjAirportObj.Location = reader["location"].ToString();

                        adjAirportList.Add(adjAirportObj);
                    }
                }
                catch(Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
            return adjAirportList;
        }
        #endregion
        #endregion

        #region getSelectedAirport
        public Airport getSelectedAirport(int idairport)
        {
            SqlDataReader reader = null;
            string query = "SELECT * FROM Airport WHERE idairport = " + "'" + idairport + "'";
            Airport airportObj = null;

            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    airportObj = new Airport();
                    airportObj.IdAirport = int.Parse(reader["idairport"].ToString());
                    airportObj.Name = reader["name"].ToString();
                    airportObj.Acronym = reader["acronym"].ToString();
                    airportObj.PhoneNo = reader["phoneno"].ToString();
                    airportObj.Location = reader["location"].ToString();
                }
                
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return airportObj;
        }
        #endregion

        #region searchNameAllAirports
        public List<Airport> searchAllAirports(string search)
        {
            SqlDataReader reader = null;
            string query = "SELECT name FROM Airport WHERE name LIKE '%" + search + "%'";
            List<Airport> searchedAirports = new List<Airport>();
            try
            {


                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {

                    Airport airportObj = new Airport();
                    airportObj.Name = reader["name"].ToString();
                    searchedAirports.Add(airportObj);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }

            return searchedAirports;
        }
        #endregion

        #region getAllAirports
        public List<Airport> GetAllAirports()
        {
            SqlDataReader reader = null;
            List<Airport> airports = new List<Airport>();
            string query = "SELECT * FROM Airport";

            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Airport airportObj = new Airport();

                    airportObj.IdAirport = int.Parse(reader["idairport"].ToString());
                    airportObj.Name = reader["name"].ToString();
                    airportObj.Acronym = reader["acronym"].ToString();
                    airportObj.PhoneNo = reader["phoneno"].ToString();
                    airportObj.Location = reader["location"].ToString();
                    //airportObj = new Airport(int.Parse(reader["idairport"].ToString()), reader["name"].ToString(), reader["acronym"].ToString(), reader["phoneno"].ToString(), reader["location"].ToString());

                    airports.Add(airportObj);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }

            return airports;
        }
        #endregion

    }
}
